/**  
 * All rights Reserved, Designed By <a href="www.itttq.com" target="_blank">www.itttq.com</a>
 * @date:   2018年1月31日 下午3:01:08
 * @Copyright: 2018 <a href="www.itttq.com" target="_blank">www.itttq.com</a> Inc. All rights reserved. 
 */  
package com.g2room.db.ddl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Random;

/**   
 * @ClassName:  SqlOp
 * @author: 迷蝶
 * @date:   2018年1月31日 下午3:01:08 
 *     
 * @Copyright: 2018 <a href="www.itttq.com" target="_blank">www.itttq.com</a> Inc. All rights reserved. 
 */
public class SqlOp {
	public static void main(String[] args) {
		initHouseData();
	}
	
	public static void initHouseData() {
		PreparedStatement grapPs = null;
		try {
			String sql = "INSERT INTO `ld-ic-ol`.`b_house` ( `floor`, `serial_number`, `house_number`, `status_code`, `status_name`, `room_area`, `building_id`, `building_name`) VALUES (?, ?,?, ?,?, ?,?, ?)";
			grapPs = getConn().prepareStatement(sql);
			// k为楼栋数 i为楼层数 j为每层户数
			for(int k =1; k< 21; k++) {
			for(int i =1; i< 29; i++) {
				for(int j = 1; j< 7; j++) {
					grapPs.setInt(1, i);
					grapPs.setInt(2, j);
					int s = i*j % 3 + 1;
					String sn = "";
					grapPs.setString(3, i+"-" +j);
					grapPs.setInt(4, s);
					if(s == 0) {
						sn = "正常入住";
					} else if(s == 1) {
						sn = "未接房";
					} else {
						sn = "空置";
					}
					grapPs.setString(5, sn);
					Random rand = new Random();
					grapPs.setString(6, (rand.nextInt(60) + 50) + "平米");
					grapPs.setInt(7, k);
					grapPs.setString(8, k + "号楼");
					grapPs.addBatch();
				}
			}
			}
			grapPs.executeBatch();
			grapPs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (null != grapPs) {
				try {
					grapPs.close();
				} catch (SQLException e) {

				}
			}
		}
		System.out.println("生成完毕");
	}
	/**
	 * 获取数据库连接对象，这里只获得，需要自己在使用后进行关闭。
	 * 此处单线程执行，要并发则使用连接池
	 *
	 * @return {Connection}
	 * @auther <a href="mailto:gaoxg@FOUNDER.COM.CN">Gao Xuguo</a>
	 * 2013-2-25 下午04:36:46
	 */
	public static Connection getConn() {
		Connection conn = null;
		try {
		    //1.加载驱动
		    Class.forName("com.mysql.jdbc.Driver");
		    //2.得到连接
		    conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ld-ic-ol?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull",
		    		"root",
		    		"123456");
		} catch(Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 关闭连接。
	 *
	 * @param {Connection} conn
	 * @auther <a href="mailto:gaoxg@FOUNDER.COM.CN">Gao Xuguo</a>
	 * 2013-2-25 下午04:39:25
	 */
	public static void closeConn(Connection conn) {
		if(null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
